from pymysql import Connection
from data_define import Record
from file_define import TextFileReader,JsonFileReader

# 建立连接
connection = Connection(
    host='localhost',  # MySQL 主机
    port=3306,      # 端口号
    user='root',  # 用户名
    password='wl@1234',  # 密码
    autocommit=True
)
# print(connection.get_host_info())       # 测试链接是否成功

cursor = connection.cursor()        # 获取游标对象

# 创建数据库
try:
    cursor.execute("CREATE DATABASE py_sql charset utf8")
    print("Database 'py_sql' created successfully")
except Exception as e:
    try:
        # 选择数据库
        connection.select_db('py_sql')
        # 创建表
        cursor.execute("CREATE TABLE orders(order_date date,order_id varchar(255),money int,province varchar(10))")
        print("TABLE 'orders' created successfully")
    except Exception as e:
        print("TABLE has exist")
    print("DATABASE has exist")

# 读取文件
text_file_reader = TextFileReader("2011年1月销售数据.txt")
json_file_reader = JsonFileReader("2011年2月销售数据JSON.txt")
jan_data:list[Record] = text_file_reader.read_data()
feb_data:list[Record] = json_file_reader.read_data()

# 合并两个月的数据
all_data:list[Record] = jan_data + feb_data

# 组织 SQL 语句
for record in all_data:
    # 封装 sql 语句
    sql = f"insert into orders(order_date,order_id,money,province)"\
            f"values('{record.date}','{record.order_id}',{record.money},'{record.province}')"
    # print(sql)    # 测试封装的 sql 语句
    # 执行 sql 语句
    cursor.execute(sql)
# 关闭连接
# cursor.close()
print("MySQL connection is closed")
